{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn.model_selection import train_test_split\n",
    "from sklearn.metrics import mean_squared_error\n",
    "from lightgbm import LGBMRegressor\n",
    "from sklearnex import patch_sklearn\n",
    "import joblib\n",
    "# 设置支持中文的字体\n",
    "plt.rcParams['font.sans-serif'] = ['SimHei']  # 指定默认字体为黑体\n",
    "# 解决负号显示为方块的问题\n",
    "plt.rcParams['axes.unicode_minus'] = False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_data = pd.read_csv('filtered_data/month_10.csv')\n",
    "# all_data = pd.read_csv('filtered_data/month_9.csv')\n",
    "data = all_data.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "数据采集时间            0\n",
       "车辆状态              0\n",
       "充电状态              0\n",
       "车速                0\n",
       "累计里程              0\n",
       "总电压               0\n",
       "总电流               0\n",
       "SOC               0\n",
       "DC-DC状态           0\n",
       "绝缘电阻              0\n",
       "驱动电机控制器温度        77\n",
       "驱动电机转速           77\n",
       "驱动电机转矩           77\n",
       "驱动电机温度           77\n",
       "电机控制器输入电压        77\n",
       "电机控制器直流母线电流      77\n",
       "电池单体电压最高值         0\n",
       "电池单体电压最低值         0\n",
       "最高温度值             0\n",
       "最低温度值             0\n",
       "最高报警等级         1349\n",
       "dtype: int64"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 合并数据集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "if not os.path.exists('filtered_data/months1-10.csv'):\n",
    "    data1 = pd.read_csv('filtered_data/months1-9.csv')\n",
    "    data2 = pd.read_csv('filtered_data/month_10.csv')\n",
    "    data0 = pd.concat([data1, data2], axis=0)\n",
    "    data0.to_csv('filtered_data/months1-10.csv', index=False)\n",
    "else:\n",
    "    data0 = pd.read_csv('filtered_data/months1-10.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 缺失值处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 删除最高报警等级"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 使用LightGBM填充缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from utils import change_df_sec\n",
    "to_processed_columns = ['驱动电机控制器温度', '驱动电机温度',\n",
    "                        '电机控制器输入电压', '电机控制器直流母线电流',\n",
    "                        '驱动电机转速', '驱动电机转矩'\n",
    "                        ]\n",
    "best_models = {}\n",
    "best_mses = {}\n",
    "\n",
    "data0['数据采集时间'] = change_df_sec(data0['数据采集时间'])\n",
    "data0 = data0.drop(['最高报警等级'], axis=1)\n",
    "data = data0.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "for column_name in to_processed_columns:\n",
    "    print(f\"Processing column: {column_name}\")\n",
    "    # 删除其他需要预测列的缺失值\n",
    "    data = data0.dropna(\n",
    "        subset=[col for col in to_processed_columns if col != column_name])\n",
    "    X = data.drop(column_name, axis=1)\n",
    "    y = data[column_name]\n",
    "    print(\"X shape:\", X.shape)\n",
    "    print(\"y shape:\", y.shape)\n",
    "    # 划分数据集\n",
    "    X_train, X_valid, y_train, y_valid = train_test_split(\n",
    "        X, y, test_size=0.2, random_state=42)\n",
    "    best_mse = np.inf\n",
    "    best_model = None\n",
    "    # for nl in [1023,2047,4095]: 2047 目前是最好的\n",
    "    for nl in [2047]:\n",
    "        model = LGBMRegressor(\n",
    "            objective='regression', metric='rmse',\n",
    "            boosting_type='gbdt', random_state=42,\n",
    "            learning_rate=0.05, num_leaves=nl, n_estimators=1000)\n",
    "        model.fit(X_train, y_train)\n",
    "        y_pred = model.predict(X_valid)\n",
    "        mse = mean_squared_error(y_valid, y_pred)\n",
    "        print(f\"Mean Squared Error: {mse}\")\n",
    "        if mse < best_mse:\n",
    "            best_mse = mse\n",
    "            best_model = model\n",
    "    print(f\"Best Mean Squared Error: {best_mse}\")\n",
    "    print(f\"Best Model: {best_model}\")\n",
    "    if best_mse < best_mses.get(column_name, np.inf):\n",
    "        best_models[column_name] = best_model\n",
    "        best_mses[column_name] = best_mse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model for 驱动电机控制器温度 saved to model/驱动电机控制器温度.pkl\n",
      "Model for 驱动电机温度 saved to model/驱动电机温度.pkl\n",
      "Model for 电机控制器输入电压 saved to model/电机控制器输入电压.pkl\n",
      "Model for 电机控制器直流母线电流 saved to model/电机控制器直流母线电流.pkl\n"
     ]
    }
   ],
   "source": [
    "for column, model in best_models.items():\n",
    "    model_path = f'model/{column}.pkl'\n",
    "    joblib.dump(model, model_path)\n",
    "    print(f\"Model for {column} saved to {model_path}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 预测"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 加载模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "for column_name in to_processed_columns:\n",
    "    best_models[column_name] = joblib.load(\n",
    "        f'model/{column_name}_best_lgb_model.pkl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Processing column: 驱动电机控制器温度\n",
      "(0, 20)\n",
      "Other columns: ['驱动电机温度', '电机控制器输入电压', '电机控制器直流母线电流', '驱动电机转速', '驱动电机转矩']\n",
      "No missing values found for column: 驱动电机控制器温度\n",
      "Processing column: 驱动电机温度\n",
      "(0, 20)\n",
      "Other columns: ['驱动电机控制器温度', '电机控制器输入电压', '电机控制器直流母线电流', '驱动电机转速', '驱动电机转矩']\n",
      "No missing values found for column: 驱动电机温度\n",
      "Processing column: 电机控制器输入电压\n",
      "(0, 20)\n",
      "Other columns: ['驱动电机控制器温度', '驱动电机温度', '电机控制器直流母线电流', '驱动电机转速', '驱动电机转矩']\n",
      "No missing values found for column: 电机控制器输入电压\n",
      "Processing column: 电机控制器直流母线电流\n",
      "(0, 20)\n",
      "Other columns: ['驱动电机控制器温度', '驱动电机温度', '电机控制器输入电压', '驱动电机转速', '驱动电机转矩']\n",
      "No missing values found for column: 电机控制器直流母线电流\n",
      "Processing column: 驱动电机转速\n",
      "(123464, 20)\n",
      "Other columns: ['驱动电机控制器温度', '驱动电机温度', '电机控制器输入电压', '电机控制器直流母线电流', '驱动电机转矩']\n",
      "Processing column: 驱动电机转矩\n",
      "(123464, 20)\n",
      "Other columns: ['驱动电机控制器温度', '驱动电机温度', '电机控制器输入电压', '电机控制器直流母线电流', '驱动电机转速']\n"
     ]
    }
   ],
   "source": [
    "preds_data = {}\n",
    "for column_name in to_processed_columns:\n",
    "    print(f\"Processing column: {column_name}\")\n",
    "\n",
    "    # 复制数据\n",
    "    data = data0.__deepcopy__()\n",
    "\n",
    "    # 只保留要预测列的空值\n",
    "    data_to_predict = data[pd.isna(data[column_name])]\n",
    "\n",
    "    print(data_to_predict.shape)\n",
    "\n",
    "    # 删除其他预测列的空值\n",
    "    other_columns = [col for col in to_processed_columns if col != column_name]\n",
    "    print(f\"Other columns: {other_columns}\")\n",
    "    data = data.dropna(subset=other_columns)\n",
    "\n",
    "    if not data_to_predict.empty:\n",
    "\n",
    "        # 所有的object数据类型转换为int类型\n",
    "        for col in data_to_predict.columns:\n",
    "            if data_to_predict[col].dtype == 'object':\n",
    "                data_to_predict[col] = data_to_predict[col].astype(int)\n",
    "\n",
    "        # 预测\n",
    "        preds_data[column_name] = best_models[column_name].predict(\n",
    "            data_to_predict.drop(columns=[column_name]))\n",
    "\n",
    "        # 将预测值填充回原数据\n",
    "        data0.loc[data_to_predict.index,\n",
    "                  column_name] = preds_data[column_name]\n",
    "    else:\n",
    "        print(f\"No missing values found for column: {column_name}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Model for 驱动电机控制器温度 saved to model/驱动电机控制器温度.pkl\n",
      "Model for 驱动电机温度 saved to model/驱动电机温度.pkl\n",
      "Model for 电机控制器输入电压 saved to model/电机控制器输入电压.pkl\n",
      "Model for 电机控制器直流母线电流 saved to model/电机控制器直流母线电流.pkl\n",
      "Model for 驱动电机转速 saved to model/驱动电机转速.pkl\n",
      "Model for 驱动电机转矩 saved to model/驱动电机转矩.pkl\n"
     ]
    }
   ],
   "source": [
    "for column, model in best_models.items():\n",
    "    model_path = f'model/{column}.pkl'\n",
    "    joblib.dump(model, model_path)\n",
    "    print(f\"Model for {column} saved to {model_path}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 保存结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "data0['最高报警等级'] = pd.read_csv('filtered_data/months1-10.csv')['最高报警等级']\n",
    "data0.to_csv('processed_data/months1-10_non_processed.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "只保存第十个月的数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original month 10 data shape: (1349, 21)\n",
      "Processed month 10 data shape: (1349, 21)\n"
     ]
    }
   ],
   "source": [
    "# Read the original month 10 data\n",
    "data10 = pd.read_csv('filtered_data/month_10.csv')\n",
    "\n",
    "# Get the range of values in 累计里程 for month 10 to use as a filter\n",
    "min_mileage = data10['累计里程'].min()\n",
    "max_mileage = data10['累计里程'].max()\n",
    "\n",
    "# Filter the processed data (data0) to get only month 10 data based on mileage range\n",
    "month10_processed = data0[(data0['累计里程'] >= min_mileage)\n",
    "                          & (data0['累计里程'] <= max_mileage)]\n",
    "\n",
    "# Save the processed month 10 data\n",
    "month10_processed.to_csv(\n",
    "    'processed_data/month10_non_processed.csv', index=False)\n",
    "\n",
    "# Print some info to verify\n",
    "print(f\"Original month 10 data shape: {data10.shape}\")\n",
    "print(f\"Processed month 10 data shape: {month10_processed.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 时间处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 复制原始数据以避免修改源数据\n",
    "data = pd.read_csv('filtered_data/months1-10.csv')\n",
    "\n",
    "# 确保数据按采集时间排序\n",
    "data = data.sort_values('数据采集时间')\n",
    "\n",
    "# 检查时间列是否为正确的datetime格式\n",
    "if not pd.api.types.is_datetime64_any_dtype(data['数据采集时间']):\n",
    "    data['数据采集时间'] = pd.to_datetime(data['数据采集时间'], errors='coerce')\n",
    "\n",
    "# 筛选出有效时间戳的数据进行处理\n",
    "data_with_time = data.dropna(subset=['数据采集时间'])\n",
    "\n",
    "# 按分钟对数据进行分组\n",
    "# 获取数据集中的所有唯一分钟\n",
    "grouped = data_with_time.groupby(data_with_time['数据采集时间'].dt.floor('min'))\n",
    "\n",
    "# 处理每个分钟组以分配秒数\n",
    "for minute, group in grouped:\n",
    "    # 获取该分钟内的记录数量\n",
    "    n_records = len(group)\n",
    "\n",
    "    # 计算要分配的秒数 (0 到 59)\n",
    "    if n_records > 1:\n",
    "        # 在分钟内均匀分配秒数\n",
    "        seconds = np.linspace(0, 59, n_records)\n",
    "    else:\n",
    "        # 如果只有一条记录，将其秒数设为0\n",
    "        seconds = [0]\n",
    "\n",
    "    # 更新该分钟组中每条记录的datetime\n",
    "    for i, idx in enumerate(group.index):\n",
    "        # 创建带有计算秒数的新datetime\n",
    "        new_time = minute + pd.Timedelta(seconds=int(seconds[i]))\n",
    "        new_time = new_time.strftime('%Y-%m-%d %H:%M:%S')\n",
    "        data.at[idx, '数据采集时间'] = new_time"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 保存结果"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "data0['数据采集时间'] = data['数据采集时间']\n",
    "data0.to_csv('processed_data/months1-10_time_processed.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Original month 10 data shape: (1349, 21)\n",
      "Processed month 10 data shape: (1349, 21)\n"
     ]
    }
   ],
   "source": [
    "data10 = pd.read_csv('filtered_data/month_10.csv')\n",
    "\n",
    "min_mileage = data10['累计里程'].min()\n",
    "max_mileage = data10['累计里程'].max()\n",
    "\n",
    "month10_processed = data0[(data0['累计里程'] >= min_mileage)\n",
    "                          & (data0['累计里程'] <= max_mileage)]\n",
    "\n",
    "month10_processed.to_csv(\n",
    "    'processed_data/month10_time_processed.csv', index=False)\n",
    "\n",
    "print(f\"Original month 10 data shape: {data10.shape}\")\n",
    "print(f\"Processed month 10 data shape: {month10_processed.shape}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "异常值处理请移步至outlier-process.ipynb"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "DataAnalysis",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.15"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
